package com.cdl.utils;

import com.cdl.utils.bean.ColumnInfo;
import com.cdl.utils.bean.ConfigProperties;
import com.cdl.utils.bean.IndexInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/****
 * 
 * 
 * JDBC工具类
 * @author cdl
 *
 */
public class JDBCUtil implements TableInfoUtil{

	private static String url ;
	private static String userName ;
	private static String password ;

	static{
		try {
			if (ConfigProperties.jdbcType.startsWith("mysql")) {
				Class.forName("com.mysql.jdbc.Driver");
			} else {
				throw new Exception("暂不支持mysql以外的数据库");
			}
			url = ConfigProperties.url;
			userName = ConfigProperties.userName;
			password = ConfigProperties.password;

		}catch (Exception e){
			e.printStackTrace();
		}
	}

	/***
	 * 获取到数据库链接
	 * @return
	 */
	public static Connection getConnection(){

		Connection conn = null ;
		try {
			conn = DriverManager.getConnection(url,userName,password);
		} catch (Exception e) {
			System.err.println("------------->数据库连接失败！");
			e.printStackTrace();
		}
		
		return conn ;
	}

	@Override
	public  boolean isExistTable(String tableName){
		Connection conn = null ;
		Statement st = null ;
		ResultSet set = null ;
		try {
			conn = getConnection();
			st =  conn.createStatement();
			//set = st.executeQuery("select 1 from user_tables where table_name = '"+tableName.toUpperCase()+"'");
			set = st.executeQuery("select 1 from information_schema.TABLES t where t.table_name = '"+tableName.toLowerCase()+"'");
			if(!set.next()){
				return false ;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
		}
		return true ;
	}
	
	
	
    /***
     * 根据表名获取表的字段信息
     * @param tableName
     * @throws SQLException
     */
	@Override
	public  List<ColumnInfo> getTableColumns(String tableName) {
		
		StringBuffer sql =  new StringBuffer();

//		sql.append(" select col.column_name, col.data_type, col.data_length,col.data_scale, con.COMMENTS");
//	    sql.append(" from user_tab_columns col, user_col_comments con");
//		sql.append(" where col.TABLE_NAME = con.TABLE_NAME");
//		sql.append("  and col.COLUMN_NAME = con.COLUMN_NAME");
//		sql.append(" and col.table_name = '"+ TableName.toUpperCase() +"'");
		sql.append(" select col.column_name" +
				"     , col.data_type" +
				"     , col.NUMERIC_PRECISION" +
				"     , col.NUMERIC_SCALE" +
				"     , col.COLUMN_COMMENT" +
				"    FROM information_schema.COLUMNS col" +
				"   WHERE col.table_name = '"+ tableName.toLowerCase() + "'"+
				"     and col.TABLE_SCHEMA = '"+ url.substring(url.lastIndexOf("/")+1, url.length()) +"' "
		);

		List<ColumnInfo> cols = new  ArrayList<ColumnInfo>();
		Connection conn = null ;
		Statement st = null ;
		ResultSet set = null ;
		try {
			conn = getConnection();
			st =  conn.createStatement();
			set = st.executeQuery(sql.toString());
			//获取所列信息
			while(set.next()){
				ColumnInfo col = new ColumnInfo();
				col.setColumnName(set.getString(1));
				col.setColumnType(set.getString(2));
				col.setColumnLength(set.getInt(3));
				col.setColumnScale(set.getInt(4));
				col.setColumnComments(set.getString(5));
				col.setSupperfiledName(StringUtil.getClassString(col.getColumnName()));
				cols.add(col);
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			
		}
		return cols ;
	}
	
	
	/***
	 * 根据表名获取表的索引信息
	 * @param tableName
	 * @throws SQLException
	 */
	@Override
	public  List<IndexInfo> getTableIndexs(String tableName) {
		
		StringBuffer sql =  new StringBuffer();
//		sql.append("    select ic.index_name         												");
//		sql.append("       , ic.column_name column_names 											");
//		sql.append("      , case 																	");
//		sql.append("           when i.UNIQUENESS = 'NONUNIQUE' then 0 								");
//		sql.append("           when i.UNIQUENESS = 'UNIQUE' then 1    								");
//		sql.append("         end isUnique  															");
//		sql.append("    from (select index_name, to_char(wm_concat(column_name)) column_name		");
//		sql.append("            from user_ind_columns												");
//		sql.append("           where table_name = '"+ TableName.toUpperCase() +"'					");
//		sql.append("           group by index_name) ic, user_indexes i								");
//		sql.append("   where ic.index_name = i.index_name											");

		sql.append("SELECT " +
				"  i.index_name, " +
				"  i.column_name column_names, " +
				"CASE " +
				"  WHEN i.index_type = 'NONUNIQUE' THEN 0   " +
				"  WHEN i.index_type = 'UNIQUE' THEN 1 ELSE 0   " +
				"  END isUnique  " +
				"FROM  " +
				"  INFORMATION_SCHEMA.STATISTICS i   " +
				"WHERE  " +
				"  table_name = '"+ tableName.toLowerCase() +"'");

		List<IndexInfo> indexs = new  ArrayList<IndexInfo>();
		Connection conn = null ;
		Statement st = null ;
		ResultSet set = null ;

		try {
			conn = getConnection();
			st =  conn.createStatement();
			set = st.executeQuery(sql.toString());
			while(set.next()){
				IndexInfo ind = new IndexInfo();
				ind.setIndexName(set.getString(1));
				ind.setColNames(set.getString(2).split(","));
				String[] arry = set.getString(2).split(",");
				for(int i=0;i<arry.length;i++){
					arry[i]=StringUtil.getFiledString(arry[i]);
				}
				ind.setFieldNames(arry);
				ind.setUnique(set.getBoolean(3));	
				indexs.add(ind);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn, st, set);
		}
		return indexs ;
	}
	

	
	/***
	 * 
	 * 关闭连接
	 * @param conn
	 * @param st
	 * @param set
	 */
	public static void close(Connection conn,Statement st,ResultSet set){

			try {
				if(conn != null){
					conn.close();
				}
				if(st != null){
					st.close();
				}
				if(set != null){
					set.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

	}
	

	
	public static void main(String[] args) throws SQLException {

		List<IndexInfo> l = new JDBCUtil().getTableIndexs("USERINFO");
		System.out.println(l);
		for(IndexInfo i:l){
			System.out.println(i.toString());
		}

		String url = "jdbc:mysql://116.62.62.79:3306/lishui_monitor";
		System.out.println(url.substring(url.lastIndexOf("/")+1, url.length()));

	}
	
	
	
	
}
